﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;

namespace nrtech.bot.compras.bll
{
    public static class Config
    {
        public static int id;
        public static String url;
        public static String userSite;
        public static String passSite;
        public static String horaMonitor;
        public static String email;
        public static String smtp;
        public static String userMail;
        public static String passMail;
        public static int porta;
        public static bool ssl;
        public static bool reenvia;
        public static String tags;
        public static int intervalo;
        public static String ultimoMonitor;

        public static void lerConfigGeral()
        {
            lerConfig(null);
        }
        
        public static void lerConfig(String usuario)
        {
            try
            {
                Constants.createConnection();
                SQLiteCommand comando = new SQLiteCommand(@"select * from config " + (usuario != null ? "where userSite = @usuario" : "limit 1"), 
                    Constants.conn);
                
                if (usuario != null)
                    comando.Parameters.AddWithValue("@usuario", usuario);

                SQLiteDataReader drConfig = comando.ExecuteReader();

                if (drConfig.HasRows)
                {
                    drConfig.Read();
                    id = Convert.ToInt32(drConfig["_id"]);
                    url = drConfig["url"].ToString();
                    userSite = drConfig["userSite"].ToString();
                    passSite = drConfig["passSite"].ToString();
                    email = drConfig["email"].ToString();
                    smtp = drConfig["smtp_email"].ToString();
                    userMail = drConfig["user_name"].ToString();
                    passMail = drConfig["password"].ToString();
                    porta = Convert.ToInt32(drConfig["porta_email"]);
                    ssl = Convert.ToBoolean(drConfig["usar_ssl"].ToString());
                    reenvia = Convert.ToBoolean(drConfig["reenviar"].ToString());
                    tags = drConfig["tags"].ToString();
                    intervalo = Convert.ToInt32(drConfig["minutos_intervalo"].ToString());

                    if (drConfig["ultimo_monitor"].ToString() != string.Empty)
                    {
                        DateTime horaExec = Convert.ToDateTime(drConfig["ultimo_monitor"].ToString());
                        ultimoMonitor = horaExec.Hour.ToString() + ":" + horaExec.Minute.ToString() + ":00";

                        ajustaHoraMonitor(Convert.ToDateTime(drConfig["ultimo_monitor"].ToString()));
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void ajustaHoraMonitor(DateTime ultima)
        {
            DateTime horaMon = ultima;
            horaMon = ultima.AddMinutes(intervalo);
            horaMonitor = horaMon.Hour.ToString() + ":" + horaMon.Minute.ToString() + ":00";
        }

        public static void salvaProximoMonitor()
        {
            Constants.createConnection();
            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"update config set "+
                @"hora_monitor = @horaMonitor, "+
                @"ultimo_monitor = @ultimo");

            comando.Connection = Constants.conn;
            comando.Parameters.AddWithValue("@horaMonitor", horaMonitor);
            comando.Parameters.AddWithValue("@ultimo", ultimoMonitor);

            trans = Constants.conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw e;
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
            }
        
        }

        public static DataSet getUsers()
        {
            DataSet retorno = new DataSet();
            Constants.createConnection();
            SQLiteCommand comando = new SQLiteCommand(@"select distinct userSite from config as usuarios order by userSite", Constants.conn);
            SQLiteDataAdapter adpUsers = new SQLiteDataAdapter(comando);

            adpUsers.Fill(retorno);
            return retorno;
        }

        public static void salvaConfigGeral()
        {
            Constants.createConnection();
            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"update config set " +
                @"hora_monitor = @horaMonitor, " +
                @"reenviar = @reenvia, " +
                @"minutos_intervalo = @intervalo, " +
                @"ultimo_monitor = @ultimo ");

            comando.Connection = Constants.conn;

            comando.Parameters.AddWithValue("@horaMonitor", horaMonitor);
            comando.Parameters.AddWithValue("@reenvia", reenvia.ToString());
            comando.Parameters.AddWithValue("@intervalo", intervalo);
            comando.Parameters.AddWithValue("@ultimo", ultimoMonitor);

            trans = Constants.conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
            }
        }

        public static void insereConfig()
        {
            Constants.createConnection();
            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"insert into config (userSite, passSite, hora_monitor, email, smtp_email, user_name, "+
                "password, porta_email, usar_ssl, reenviar, tags, minutos_intervalo, ultimo_monitor) values (" +
                "@userSite, " +
                "@passSite, " +
                "@horaMonitor, " +
                "@email, " +
                "@smtp, " +
                "@userMail, " +
                "@passMail, " +
                "@porta, " +
                "@ssl, " +
                "@reenvia, " +
                "@tags, " +
                "@intervalo, " +
                "@ultimo) ");

            comando.Connection = Constants.conn;
            comando.Parameters.AddWithValue("@userSite", userSite);

            comando.Parameters.AddWithValue("@passSite", passSite);
            comando.Parameters.AddWithValue("@horaMonitor", horaMonitor);
            comando.Parameters.AddWithValue("@email", email);
            comando.Parameters.AddWithValue("@smtp", smtp);
            comando.Parameters.AddWithValue("@userMail", userMail);
            comando.Parameters.AddWithValue("@passMail", passMail);
            comando.Parameters.AddWithValue("@porta", porta);
            comando.Parameters.AddWithValue("@ssl", ssl.ToString());
            comando.Parameters.AddWithValue("@reenvia", reenvia.ToString());
            comando.Parameters.AddWithValue("@tags", tags);
            comando.Parameters.AddWithValue("@intervalo", intervalo);
            comando.Parameters.AddWithValue("@ultimo", ultimoMonitor);

            trans = Constants.conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
            }
        }

        public static void excluirConfig(String usuario)
        {
            Constants.createConnection();
            SQLiteCommand comando = new SQLiteCommand(@"delete from config where userSite = @userSite", Constants.conn);
            comando.ExecuteNonQuery();
        }

        public static void salvarConfig(String usuario)
        {
            Constants.createConnection();
            SQLiteTransaction trans;

            SQLiteCommand comando = new SQLiteCommand(@"update config set "+
                @"userSite = @userSite, "+
                @"passSite = @passSite, "+
                @"hora_monitor = @horaMonitor, "+
                @"email = @email, "+
                @"smtp_email = @smtp, "+
                @"user_name = @userMail, "+
                @"password = @passMail, "+
                @"porta_email = @porta, "+
                @"usar_ssl = @ssl, "+
                @"reenviar = @reenvia, "+
                @"tags = @tags, "+
                @"minutos_intervalo = @intervalo, "+
                @"ultimo_monitor = @ultimo "+
                " where userSite = @usuario");

            comando.Connection = Constants.conn;
            comando.Parameters.AddWithValue("@userSite", userSite);

            comando.Parameters.AddWithValue("@passSite", passSite);
            comando.Parameters.AddWithValue("@horaMonitor", horaMonitor);
            comando.Parameters.AddWithValue("@email", email);
            comando.Parameters.AddWithValue("@smtp", smtp);
            comando.Parameters.AddWithValue("@userMail", userMail);
            comando.Parameters.AddWithValue("@passMail", passMail);
            comando.Parameters.AddWithValue("@porta", porta);
            comando.Parameters.AddWithValue("@ssl", ssl.ToString());
            comando.Parameters.AddWithValue("@reenvia", reenvia.ToString());
            comando.Parameters.AddWithValue("@tags", tags);
            comando.Parameters.AddWithValue("@intervalo", intervalo);
            comando.Parameters.AddWithValue("@ultimo", ultimoMonitor);
            comando.Parameters.AddWithValue("@usuario", usuario);

            trans = Constants.conn.BeginTransaction();
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                trans.Commit();
                comando.Dispose();
            }
        }
    }
}
